

ALTER   proc sp_close_all_finished_goods_lots (
@part_number varchar(50), 
@lot_number varchar(50),
@vendor_name varchar(50))

as
begin
	declare @process_step_id int
	declare @location_id int
	declare @lot_id int

	Select @process_step_id = Process_Step.ProcessStep_ID from product,Process_Step where @part_number like product.part_number + '%' and PS_Type_Name like 'Finished Goods' and product.ProcessStep_ID=Process_Step.ProcessStep_ID 
	Select @location_id = Location_ID from Location where L_Type = 'Finished Goods' and L_Vendor_Name = @vendor_name
	Select @lot_id = Lot_ID from Lot_Table where ProcessStep_ID = @process_step_id and Location_ID = @location_id and Lot_Number = @lot_number
	update lot_table set lot_current_status = 'finished' where lot_id = @lot_id
end


